package org.csu.mypetstore.persistence.impl;

import org.csu.mypetstore.domain.Product;
import org.csu.mypetstore.persistence.DBUtil;
import org.csu.mypetstore.persistence.ProductDao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ProductDaoImpl implements ProductDao {

    private static final String GET_PRODUCTS_LIST_BY_CATEGORY = "SELECT PRODUCTID,NAME,DESCN as description,CATEGORY as categoryId FROM PRODUCT WHERE CATEGORY=?";
    private static final String GET_PRODUCT_BY_PRODUCTID = "SELECT PRODUCTID,NAME,DESCN as description,CATEGORY as categoryId FROM PRODUCT WHERE PRODUCTID = ?";
    private static final String GET_SEARCH_PRODUCTS_LIST_BY_KEYWORDS = "SELECT PRODUCTID,NAME,DESCN as description,CATEGORY as categoryId from PRODUCT WHERE lower(name) like ?";
    private static final String GET_SEARCH_PRODUCTS_LIST_BY_KEYWORDS_WITH_LIMIT = "SELECT PRODUCTID,NAME,DESCN as description,CATEGORY as categoryId from PRODUCT WHERE lower(name) like ? limit 10";

    @Override
    public List<Product> getProductListByCategory(String categoryId) {
        List<Product>products =  new ArrayList<Product>();
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_PRODUCTS_LIST_BY_CATEGORY);
            preparedStatement.setString(1,categoryId);
            ResultSet resultSet =  preparedStatement.executeQuery();
            while (resultSet.next())
            {
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                products.add(product);
            }

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeConnection(connection);

        }catch (Exception e)
        {
            e.printStackTrace();
        }

        return products;

    }

    @Override
    public Product getProduct(String productId) {
        Product product = null ;
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_PRODUCT_BY_PRODUCTID);
            preparedStatement.setString(1,productId);
            ResultSet resultSet =  preparedStatement.executeQuery();
            if (resultSet.next())
            {
                product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
            }

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeConnection(connection);

        }catch (Exception e)
        {
            e.printStackTrace();
        }
        return product;
    }

    @Override
    public List<Product> searchProductList(String keywords) {
        List<Product>products =  new ArrayList<Product>();
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_SEARCH_PRODUCTS_LIST_BY_KEYWORDS);
            preparedStatement.setString(1,keywords);
            ResultSet resultSet =  preparedStatement.executeQuery();
            while (resultSet.next())
            {
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                products.add(product);
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeConnection(connection);

        }catch (Exception e)
        {
            e.printStackTrace();
        }

        return products;
    }

    @Override
    public List<Product> searchProductListWithLimit(String keywords) {
        List<Product>products =  new ArrayList<Product>();
        try{
            Connection connection = DBUtil.getConnection();
            PreparedStatement preparedStatement = connection.prepareStatement(GET_SEARCH_PRODUCTS_LIST_BY_KEYWORDS_WITH_LIMIT);
            preparedStatement.setString(1,keywords);
            ResultSet resultSet =  preparedStatement.executeQuery();
            while (resultSet.next())
            {
                Product product = new Product();
                product.setProductId(resultSet.getString(1));
                product.setName(resultSet.getString(2));
                product.setDescription(resultSet.getString(3));
                product.setCategoryId(resultSet.getString(4));
                products.add(product);
            }
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeConnection(connection);

        }catch (Exception e)
        {
            e.printStackTrace();
        }

        return products;
    }
}
